﻿using DDTek.Oracle;
using Excel2DbTool.Common;
using Excel2DbTool.Helpers;
using Excel2DbTool.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace Excel2DbTool.Migrater_New
{
    public class XMTableMigrater : SimpleDbTableTransactionMigrater
    {
        public static readonly string PrimeKey = SelfFieldNames.XMID;
        public static readonly string GROUPKEY = OrginTableFieldNames.PROJECTNAME;

        public static readonly string ZDDM = SelfFieldNames.ZDDM;

        IList<DbRowMappingNew> dbRowMappings;

        //---------------------------------对于顺序是有要求的---------------------------------
        //---------------------------------有匹配的放在前面-----------------------------------
        //---------------------------------不能为空，而又没匹配的则放在后面--------------------
        //原理:首先根据集合中的出现顺序检索出相应字段的值;然后将其存入目标数据库的相应字段中
        static List<DbFieldMapping> dbFieldMappings = new List<DbFieldMapping>()
        {
            //has match
            new OracleTableNotNullStrField(OrginTableFieldNames.PROJECTNAME,SelfFieldNames.XMMC),
            new OracleTableStrField(OrginTableFieldNames.CORPACCOUNTS,SelfFieldNames.XMBH),
            new OracleTableStrField(OrginTableFieldNames.CORPNAME,SelfFieldNames.KFQYMC),
            new OracleTableStrField(OrginTableFieldNames.XQMC,SelfFieldNames.XQMC),
            new OracleTableNotNullStrField(OrginTableFieldNames.HOUSEADDR,SelfFieldNames.XMZL),

            new OracleTableNumField(OrginTableFieldNames.SALEBUILDER,SelfFieldNames.ZS),
            new OracleTableNumField(OrginTableFieldNames.SUMBUILDAREA,SelfFieldNames.ZJZMJ),
            new OracleTableNumField(OrginTableFieldNames.SALEAREA,SelfFieldNames.KSMJ),
            new OracleTableNumField(OrginTableFieldNames.SALEHOUSE,SelfFieldNames.KSTS),
            new OracleTableStrField(OrginTableFieldNames.CONTACT,SelfFieldNames.XMFZR),
            new OracleTableDateTimeField(OrginTableFieldNames.MODIFYTIME,SelfFieldNames.XGSJ),

            //mapping to the other field refer in same table
            new OracleTableStrFieldReferToOtherFieldInSameTable(OrginTableFieldNames.NONE,SelfFieldNames.ZDDM,SelfFieldNames.XMMC,(xmmc) =>{
                xmmc = xmmc.Replace('\'', ' ');
                xmmc = xmmc.Trim();
                string result = ProjectZDMappingProvider.GetMatchZDNumber(xmmc);
                return string.IsNullOrEmpty(result) ? "' '":string.Format("'{0}'", result);
            }),
            
            //can not be null,but orgin table has no match；so we will not select relative data from orgin table;
            //but will give a default value to  target table
            new OracleTableIdentityStrField(OrginTableFieldNames.NONE,SelfFieldNames.XMID),//KEY
            new OracleTableStrFieldHasFixedVal(OrginTableFieldNames.NONE,SelfFieldNames.XZQDM,"150523"),
        };

        public XMTableMigrater()
        {
            dbRowMappings = new List<DbRowMappingNew>(50);
        }

        protected override IList<DbFieldMapping> DbFieldMappings
        {
            get { return dbFieldMappings; }
        }

        public void Execute()
        {
            DataTable dt = GetXMDataWhichNeedInsertToTable();

            for (int i = 0; i < dt.Rows.Count; i++)
            {

                DbRowMappingNew dbrm = GetDbRowMappingNew();

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var val = dt.Rows[i][j];
                    dbrm[j].Val = val;
                }

                dbRowMappings.Add(dbrm);
            }

            string insertSql = getInsertSqlString();

            var command = GetOracleCommandAndOpenConnect();
            var transactioInsertAction = GetTransactionInsertFunc(command);

            try
            {
                transactioInsertAction(insertSql);

                ZRZTableMigrater d = new ZRZTableMigrater(dbRowMappings);
                d.Execute(transactioInsertAction);

                command.Transaction.Commit();
            }
            catch (Excel2DbException ex)
            {
                command.Transaction.Rollback();
                throw;
            }
            finally
            {
                command.Connection.Close();
                command.Dispose();
            }
        }

        DataTable GetXMDataWhichNeedInsertToTable()
        {
            string sql = String.Format("SELECT {0} FROM {1} where rowid=any(select max(rowid) from {1} group by {2})", GetFieldStringNeedReslove(), ConfigHelper.TableName, GROUPKEY);
            return SqlHelper.GetInstance().Select(sql);
        }


        DbRowMappingNew GetDbRowMappingNew()
        {
            DbRowMappingNew dbrm = new DbRowMappingNew();

            foreach (var item in dbFieldMappings)
            {
                dbrm.AddFieldMapping(item.Clone());
            }

            return dbrm;
        }


        OracleCommand GetOracleCommandAndOpenConnect()
        {
            OracleConnection conn = new OracleConnection(ConfigHelper.DbConnectStr);
            conn.Open();

            OracleCommand command = new OracleCommand();

            OracleTransaction trans = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

            command.Transaction = trans;
            command.Connection = conn;

            return command;
        }




        void InsertDataToDb()
        {
            string insertSql = getInsertSqlString();
            SqlHelper.GetInstance().Insert(insertSql);
        }

        string getInsertSqlString()
        {
            if (dbRowMappings.Count == 0)
            {
                return string.Empty;
            }

            StringBuilder sb = new StringBuilder(20000);
            sb.AppendFormat("INSERT INTO {0} ({1})", DbTableNames.BDC_XM, GetFieldStringNeedInsert());


            for (int i = 0; i < dbRowMappings.Count(); i++)
            {
                sb.AppendFormat(" SELECT {0} FROM dual {1}", dbRowMappings.ElementAt(i).GetValStr(), (i == dbRowMappings.Count() - 1) ? "" : "union");
            }

            return sb.ToString();
        }

        class SelfFieldNames
        {
            internal static readonly string XMID = "XMID";
            internal static readonly string XZQDM = "XZQDM";
            internal static readonly string ZDDM = "ZDDM";
            internal static readonly string XMMC = "XMMC";
            internal static readonly string XMBH = "XMBH";
            internal static readonly string KFQYMC = "KFQYMC";
            internal static readonly string KFQYZZJGDMZ = "KFQYZZJGDMZ";
            internal static readonly string XQMC = "XQMC";
            internal static readonly string XMZL = "XMZL";
            internal static readonly string ZS = "ZS";
            internal static readonly string ZJZMJ = "ZJZMJ";
            internal static readonly string ZTS = "ZTS";
            internal static readonly string KSMJ = "KSMJ";
            internal static readonly string KSTS = "KSTS";
            internal static readonly string ZZTS = "ZZTS";
            internal static readonly string FZZTS = "FZZTS";
            internal static readonly string GYFWMJ1 = "GYFWMJ1";
            internal static readonly string GYFWMJ2 = "GYFWMJ2";
            internal static readonly string FTMJ = "FTMJ";
            internal static readonly string XMFZR = "XMFZR";
            internal static readonly string XMFZRZJZL = "XMFZRZJZL";
            internal static readonly string XMFZRZJH = "XMFZRZJH";
            internal static readonly string JSGCGHXKZ = "JSGCGHXKZ";
            internal static readonly string JSGCYDGHXKZ = "JSGCYDGHXKZ";
            internal static readonly string SGXKZ = "SGXKZ";
            internal static readonly string SGDW = "SGDW";
            internal static readonly string KGRQ = "KGRQ";
            internal static readonly string JGRQ = "JGRQ";
            internal static readonly string TDXZ = "TDXZ";
            internal static readonly string TDKSSYRQ = "TDKSSYRQ";
            internal static readonly string TDJSSYRQ = "TDJSSYRQ";
            internal static readonly string CJSJ = "CJSJ";
            internal static readonly string CJR = "CJR";
            internal static readonly string XGSJ = "XGSJ";
            internal static readonly string XGR = "XGR";
            internal static readonly string BZ = "BZ";
            internal static readonly string ZDID = "ZDID";


            /* powershell script
                
                $filePath =  "d:\test.txt "
                #Remove-Item $filePath

                $content = Get-Content "C:\Users\LQ\Desktop\d.txt" ;
                $lst = $content.Split([System.Environment]::NewLine,[StringSplitOptions]::RemoveEmptyEntries);
                $str = "";
                for ($i = 0; $i -lt $lst.Count; $i++)
                { 
                $itemVal = $lst[$i].ToUpper().Trim();
              
                $str += [String]::Format('internal static readonly string {0} = "{0}";',$itemVal)+[System.Environment]::NewLine;
                }

                $str >> $filePath
             
             */

        }
    }


    /*
 -- Create table
create table BDC_XM
(
  xmid        VARCHAR2(36) not null,
  xzqdm       VARCHAR2(12) not null,
  zddm        VARCHAR2(19) not null,
  xmmc        VARCHAR2(100) not null,
  xmbh        VARCHAR2(50),
  kfqymc      VARCHAR2(100),
  kfqyzzjgdmz VARCHAR2(50),
  xqmc        VARCHAR2(50),
  xmzl        VARCHAR2(200) not null,
  zs          INTEGER,
  zjzmj       FLOAT,
  zts         INTEGER,
  ksmj        FLOAT,
  ksts        INTEGER,
  zzts        INTEGER,
  fzzts       INTEGER,
  gyfwmj1     FLOAT,
  gyfwmj2     FLOAT,
  ftmj        FLOAT,
  xmfzr       VARCHAR2(20),
  xmfzrzjzl   VARCHAR2(2),
  xmfzrzjh    VARCHAR2(50),
  jsgcghxkz   VARCHAR2(50),
  jsgcydghxkz VARCHAR2(50),
  sgxkz       VARCHAR2(50),
  sgdw        VARCHAR2(100),
  kgrq        DATE,
  jgrq        DATE,
  tdxz        VARCHAR2(4),
  tdkssyrq    DATE,
  tdjssyrq    DATE,
  cjsj        DATE,
  cjr         VARCHAR2(50),
  xgsj        DATE,
  xgr         VARCHAR2(50),
  bz          VARCHAR2(1000),
  zdid        VARCHAR2(12)
)
tablespace BDCDJ0616
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 128K
    next 8K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table BDC_XM
  is '项目';
-- Add comments to the columns 
comment on column BDC_XM.xmid
  is '项目ID';
comment on column BDC_XM.xzqdm
  is '行政区代码';
comment on column BDC_XM.zddm
  is '宗地代码';
comment on column BDC_XM.xmmc
  is '项目名称';
comment on column BDC_XM.xmbh
  is '项目编号';
comment on column BDC_XM.kfqymc
  is '开发企业名称';
comment on column BDC_XM.kfqyzzjgdmz
  is '开发企业组织机构代码证';
comment on column BDC_XM.xqmc
  is '小区名称';
comment on column BDC_XM.xmzl
  is '项目坐落';
comment on column BDC_XM.zs
  is '幢数';
comment on column BDC_XM.zjzmj
  is '总建筑面积';
comment on column BDC_XM.zts
  is '总套数';
comment on column BDC_XM.ksmj
  is '可售面积';
comment on column BDC_XM.ksts
  is '可售套数';
comment on column BDC_XM.zzts
  is '住宅套数';
comment on column BDC_XM.fzzts
  is '非住宅套数';
comment on column BDC_XM.gyfwmj1
  is '公用房屋面积';
comment on column BDC_XM.gyfwmj2
  is '共有房屋面积';
comment on column BDC_XM.ftmj
  is '分摊面积';
comment on column BDC_XM.xmfzr
  is '项目负责人';
comment on column BDC_XM.xmfzrzjzl
  is '项目负责人证件种类';
comment on column BDC_XM.xmfzrzjh
  is '项目负责人证件号';
comment on column BDC_XM.jsgcghxkz
  is '建设工程规划许可证';
comment on column BDC_XM.jsgcydghxkz
  is '建设工程用地规划许可证';
comment on column BDC_XM.sgxkz
  is '施工许可证';
comment on column BDC_XM.sgdw
  is '施工单位';
comment on column BDC_XM.kgrq
  is '开工日期';
comment on column BDC_XM.jgrq
  is '竣工日期';
comment on column BDC_XM.tdxz
  is '土地性质';
comment on column BDC_XM.tdkssyrq
  is '土地开始使用日期';
comment on column BDC_XM.tdjssyrq
  is '土地结束使用日期';
comment on column BDC_XM.cjsj
  is '创建时间';
comment on column BDC_XM.cjr
  is '创建人';
comment on column BDC_XM.xgsj
  is '修改时间';
comment on column BDC_XM.xgr
  is '修改人';
comment on column BDC_XM.bz
  is '备注';
comment on column BDC_XM.zdid
  is '宗地ID';
-- Create/Recreate primary, unique and foreign key constraints 
alter table BDC_XM
  add constraint BDC_XM_XMID_PRI primary key (XMID)
  using index 
  tablespace BDCDJ0616
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
     
     */
}
